【データ統合基盤 CS アナリティクス】 BigQuery はじめました！ ～CSA JMCでBigQuery連携を行うための設定手順～
こんにちは、みかみです。
弊社クラスメソッドの自社プロダクト CS アナリティクス（以下 CSA ）は、短期間、低コストで導入可能な統合データ分析基盤です。
このたび、CSA が Google BigQuery（以下 BigQuery ） に対応しました！
概要
CSA では以下の構成要素を登録し、登録した構成要素を組み合わせてジョブを作成します。
- データ連携：ファイルストレージから DWH へのデータロード
- SQL：SQL の実行
- プログラム：Python プログラムの実行
ジョブはスケジュール実行可能で、実行完了時にメール通知するなどの設定もできます。
本エントリでは、CSA から BigQuery にアクセスする際の設定手順と、Google Cloud Storage（以下 GCS ） のファイルデータを BigQuery にロードする CSA のジョブを作成して実行してみます。
CSA JMCの挙動確認バージョン
当エントリの内容は以下のCSA JMCバージョンで挙動を確認しています。
- CSA JMC v5.0.0
Google Cloud Platform のリソースを準備
CSA からアクセスする Google Cloud Platform（以下 GCP ）のリソースを作成します。
GCP アカウントと課金が有効になったプロジェクトは作成済みであるものとします。
BigQuery のデータセットを作成
CSA からアクセスする BigQuery のデータセットを作成します。
GCP 管理コンソールにログイン後、ナビゲーションメニューから BigQuery をクリックします。 リソース項目右端のピンマークをクリックすると、項目をナビゲーションメニュー上部に固定表示することができるので、よく使うリソースは固定しておくと便利です。
左下のリストからプロジェクトを選択して、「データセットを作成」ボタンをクリックします。
データセット作成画面で「データセット ID」を入力し、プルダウンから「データのロケーション」を選択し、「データセットを作成」ボタンをクリック。 「デフォルトのテーブルの有効期限」は任意で変更可能ですが、有効期限を指定した場合、期限に達したテーブルは自動的に削除されてしまうのでご注意ください。
データセットが作成できました。
GCS のバケットを作成してロードするファイルを配置
BigQuery にロードするデータを格納する GCS バケットを作成し、ファイルを配置します。
GCP 管理コンソールナビゲーションメニューから Storage をクリックします。
画面上部の「バケットを作成」リンクをクリック。
バケット作成画面でバケット名を入力して「続行」
「データの保存場所の選択」で、先ほど作成した BigQuery のデータセットと同じロケーションを選択して「続行」
ストレージクラスの選択、アクセス制御設定、詳細設定は必要に応じて変更してください。 今回はデフォルトのまま「作成」ボタンをクリック。
GCS バケットが作成できました。 「フォルダを作成」ボタンをクリックしてデータファイルを配置するディレクトリを任意の名前で作成します。
作成したディレクトリに移動後、「ファイルをアップロード」ボタンをクリックしてデータファイルをアップロードします。
以下のサンプルデータファイルを gs://csa-mikami/dogs/ 配下にアップロードしました。
id,name 1,シェパード 2,シベリアンハスキー 3,秋田犬
サービスアカウントとアカウントキーファイルを作成
CSA から GCP へのアクセスには、サービスアカウントを使用します。 BigQuery と GCS へのアクセス権を付与したサービスアカウントを作成し、アカウントキーフィルを取得します。
GCP 管理コンソールナビゲーションメニュー「IAM と管理」から「サービスアカウント」をクリックします。
「サービスアカウントを作成」リンクをクリック。
「サービスアカウント名」に任意のアカウント名を入力し「作成」をクリック。
アカウントの権限設定画面で、ロール選択プルダウンから「BigQuery 管理者」と「ストレージ管理者」のロールを選択したら、「続行」
次のアクセス権付与設定画面では、サービスアカウントの管理ユーザーを設定したい場合には必要に応じて設定してください。 今回は設定せずに「完了」ボタンをクリック。
サービスアカウントが作成できました。
続いてアカウントキーファイルを取得します。
サービスアカウント一覧画面で、先ほど作成したサービスアカウントの右端の「操作」アイコンをクリックし。「鍵を作成」リンクをクリックします。
秘密鍵の作成ポップアップで「キーのタイプ」は「JSON」のままで「作成」リンクをクリック。
秘密鍵の JSON ファイルがローカル PC に保存されました。
CSA で BigQuery 接続設定と構成要素設定
CSA の「サイト管理」メニュー「サイト設定」から、BigQuery 接続情報と GCS バケットを設定します。
BigQuery 接続情報を設定
「BigQuery接続」タブをクリックして、「BigQueryプロジェクトID」に GCP プロジェクト ID を入力、「アカウントキーファイル」項目は「変更」ボタンから、サービスアカウント作成時に取得したアカウントキーファイルをアップロードします。
「接続テスト」ボタンをクリックして「接続に成功しました。」表示が出れば OK。「保存」ボタンで BigQuery 接続情報を保存します。
GCS バケットを設定
続いて「構成要素設定」タブをクリックして、GCS バケットを設定します。
「アカウントキーファイル」項目には、先ほど「BigQuery接続」画面で設定したアカウントキーファイル名が表示され、「サービスアカウントEメール」にはサービスアカウント作成時に付与された [サービスアカウント名]@[プロジェクト名].iam.gserviceaccount.com が表示されているはずです。
もしアカウントキー項目に何も表示されていない場合は BigQuery 接続情報が保存されていません。
また、「構成要素設定」画面でサービスアカウント設定を変更すると、「BigQuery接続」のサービスアカウントも連動して変更されるのでご注意ください。
「データ連携用GCSバケット」「SQL用GCSバケット」「プログラム用GCSバケット」に、GCP 管理コンソールから作成したバケット名を入力して、「保存」ボタンをクリック。
「データ連携用GCSバケット」は BigQuery にロードするファイルを配置するバケット、「SQL用GCSバケット」は CSA の構成要素として登録する SQL を保管するバケット、「プログラム用GCSバケット」はCSA の構成要素として登録する Python プログラムを保管するバケットです。 それぞれ別バケットを指定することも可能ですが、今回は同一バケットを設定します。 「データ連携用GCSバケット」には、BigQuery のデータセットと同じロケーションのバケットを設定する必要があるのでご注意ください。
接続テストの後、GCS バケットの設定が完了しました。
構成要素とジョブを作成して実行
CSA でデータ連携の構成要素を登録し、ジョブで実行します。
データ連携の構成要素を作成
「構成要素」メニューから「データ連携」をクリックします。
データ連携画面で「作成」タブをクリックし、データ連携の構成要素を作成します。
データ連携新規作成画面で、以下の項目を選択または入力して「保存」します。
- データセット名
- テーブル名
- GCSファイルパス
- 取り込み方式
- ファイルの種類
- ファイル囲み文字（「ファイルの種類」で「CSV」を選択した場合のみ）
- 区切り文字（「ファイルの種類」で「CSV」を選択した場合のみ）
- GZip圧縮有無
「テーブル名」は既存のテーブルから選択することも可能ですが、新規テーブル名を入力することもできます。 新規テーブルにデータをロードする場合、「テーブルが存在しない場合新規作成する」項目を ON に設定してください。
また、「GCSファイルパス」はロードファイル配置場所に合わせて設定する必要がありますが、「GCSファイルパスを固定指定にする」が OFF の場合、ロードファイルは /YYYY/MM/DD/ 配下に配置する必要があります。
今回は固定パス（ gs://csa-mikami/dogs/ ）に配置したファイルをロードするため、「GCSファイルパスを固定指定にする」を ON に変更後、「GUIで選択」ボタンから dogs/ を「GCSファイルパス」に指定しました。
正常に完了すると、作成した構成要素がデータ連携一覧に追加されます。
データロードジョブを作成
続いてジョブを作成し、構成要素として先ほど作成したデータ連携の構成要素を登録します。
「ジョブ」メニューの「ジョブ一覧」をクリックします。
ジョブ一覧画面で「ジョブの追加」ボタンをクリックし、「ジョブの追加」ポップアップ「ジョブ名」に任意のジョブ名を入力して「追加」ボタンをクリック。 「ジョブ実行種別」では「構成要素を実行」と「ジョブを実行」の 2 種類が選択可能です。 複数のジョブを作成した後、各ジョブを実行するジョブを作成することもできますが、今回はデータ連携の構成要素を実行するジョブを追加するため、「ジョブ実行種別」には「構成要素を実行」を設定しておきます。
ジョブ詳細画面の下の方にある「構成要素」欄の「編集」ボタンをクリック。
構成要素の編集画面で、「設定可能な構成要素」欄に表示されたデータ連携の構成要素を「現在の構成要素」欄に移動し「保存」します。
ジョブ詳細画面に戻ったら画面の一番下にある「保存」ボタンをクリック。
ジョブ一覧画面に遷移すると、新規作成したジョブが一覧に追加されているのが確認できました。
作成したジョブを実行
作成したジョブはスケジュール実行も可能ですが、今回は動作確認のために手動実行してみます。
ジョブ一覧画面で、実行したいジョブの列の右端にある「▶」アイコンをクリックします。 表示される「今すぐジョブを実行」ポップアップで「確認」ボタンをクリック。
「ジョブ実行確認」ポップアップで内容を確認して「実行」します。
「カスタム実行を開始しました。」メッセージが表示されたのを確認して、しばらく待ちます。
「一覧を更新」アイコンをクリックし、該当ジョブの「実行履歴カウンター」欄を確認します。 実行成功である一番左の緑色の丸の中の数値が増加していれば、ジョブの正常実行が完了しました。
「実行履歴カウンター」緑色の丸のリンクから遷移可能な「ジョブ実行履歴」画面より、ジョブの実行ログを確認することも可能です。
本当に BigQuery にデータがロードされたのか、GCP 管理コンソールから確認してみます。
dogs テーブルが新規作成され、GCS ファイルのデータが正常にロードされたことが確認できました。
まとめ
CSA では以下の構成要素を組み合わせて、必要なジョブを簡単に作成することができます。
- データ連携
- SQL
- プログラム
今回はデータ連携（データロード）ジョブを作成してみましたが、データ連携と SQL を組み合わせれば、データをロードした後に SQL で必要な集計を実行することも可能です。
また、ジョブを組み合わせたジョブを作成して、処理の実行順なども柔軟に制御することができます。
BigQuery にも対応可能な統合データ分析基盤 CSA について、少しでもご興味をお持ちいただけましたら、ぜひお気軽に弊社クラスメソッドにご連絡ください！
